<?php

class Groups_m extends CI_Model {
    public function __construct() {
        parent::__construct();
        
        $this->load->database();
        $this->load->helper('date');
    }
    
    function get_all_groups() {
        $this->db->from('groups');
        $this->db->where('status !=', 'deleted');
        $this->db->order_by('groups.group_name');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_all_groups_order_date() {
        $this->db->from('groups');
        $this->db->where('status !=', 'deleted');
        $this->db->order_by('groups.date_created');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    
    function get_all_subscribable_groups() {
        $this->db->from('groups');
        $where = "is_subscribable = 1 AND status IS NOT 'deleted'";
        $this->db->where($where);
        $this->db->order_by('groups.group_name');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_all_subscribable_groups_order_date() {
        $this->db->from('groups');
        $where = "is_subscribable = 1 AND status IS NOT 'deleted'";
        $this->db->where($where);
        $this->db->order_by('groups.date_created');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    
    function get_all_UNsubscribable_groups() {
        $this->db->from('groups');
        $this->db->where('groups.is_subscribable', 0);
        $this->db->order_by('groups.group_name');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_all_UNsubscribable_groups_order_date() {
        $this->db->from('groups');
        $this->db->where('groups.is_subscribable', 0);
        $this->db->order_by('groups.date_created');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    
    function get_group($group_id) {
        $this->db->select('groups.*');
        $this->db->from('groups');
        $this->db->where('group_id', $group_id);
        $query = $this->db->get();
        
        return $query->row_array();
    }
    
    
    function insert_group($selected_emps, $can_subscribe) {
        $this->load->helper();
        
        $data = array(
                'group_name' => $this->input->post('groupName'),
                'group_desc' => $this->input->post('groupDesc'),
                'is_subscribable' => $can_subscribe,
                'date_created'=> date('Y-m-d H:i:s',now()),
                'status' => 'active'
                );
            
        $this->db->insert('groups', $data);
        
        //get groups id and insert into group_admin
        $this->db->select('group_id');
        $this->db->from('groups');
        $this->db->where('group_name', $this->input->post('groupName'));
        $query = $this->db->get();
        
        $qq2 = $query->row_array();
        $group_id = $qq2['group_id'];
        //print_r($group_id);
        
        foreach($selected_emps as $e) {
           $d = array(
                    'user_id' => $e,
                    'group_id' => $group_id 
                 );
            $this->db->insert('group_admin', $d);
        }
    }
    
    
    
    
    function update_group($group_id, $selected_emps, $data) {
        $this->db->where('group_id', $group_id);
        $this->db->update('groups', $data);
        
        $this->db->delete('group_admin', array('group_id' => $group_id)); 
        foreach($selected_emps as $e) {
            $data = array(
                        'group_id' => $group_id,
                        'user_id' => $e
            );
            
            $this->db->insert('group_admin', $data);
        }
    }
    
    
    function delete_group($group_id) {
        $this->db->where('group_id', $group_id);
        $this->db->delete('subscriber');
        
        $this->db->where('group_id', $group_id);
        $this->db->delete('group_post');
        
        $this->db->where('group_id', $group_id);
        $this->db->delete('group_admin');
        
        $data = array('status' => 'deleted');
        $this->db->where('group_id', $group_id);
        $this->db->update('groups', $data);
    }
    
    
    function getGroupAdmins($group_id) {
        $this->db->from('group_admin');
        $this->db->join('user', 'group_admin.user_id = user.user_id');
        $this->db->where('group_admin.group_id', $group_id);
        $this->db->order_by('group_admin.user_id');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    

    function get_group_id($group_name) {
        $this->db->select('group_id');
        $this->db->from('groups');
        $this->db->where('group_name', $group_name);
        $query = $this->db->get();
        
        return $query->row();
    }    
    
    
    function get_group_name($group_id) {
        $this->db->select('group_name');
        $this->db->from('groups');
        $this->db->where('group_id', $group_id);
        $query = $this->db->get();
        
        return $query->row();
    }
    
    
    function get_group_posts($group_id) {
        $this->db->select("post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, DATE(post.date_time_pub),
                post.date_time_modified,
                post.category_id, post.author, post.status,
                post.state, user.*");
        
        $this->db->from('group_post');
        $this->db->join('groups', 'group_post.group_id = groups.group_id');
        $this->db->join('post', 'group_post.post_id = post.post_id');
        $this->db->join('user', 'post.author = user.user_id');
        $where = "group_post.group_id = $group_id AND post.status = 'published'";
        $this->db->where($where);
        $this->db->order_by('post.date_time_pub DESC');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_group_posts_limit($limit, $start, $group_id) {
        $this->db->select("post.post_id, post.title, post.summary, 
                post.date_time_pub, post.full_text, DATE(post.date_time_pub),
                post.date_time_modified,
                post.category_id, post.author, post.status,
                post.state, user.*");
        
        $this->db->from('group_post');
        $this->db->join('groups', 'group_post.group_id = groups.group_id');
        $this->db->join('post', 'group_post.post_id = post.post_id');
        $this->db->join('user', 'post.author = user.user_id');
        $where = "group_post.group_id = $group_id AND post.status = 'published'";
        $this->db->where($where);
        $this->db->order_by('post.date_time_pub DESC');
        $this->db->limit($limit, $start);
        $query = $this->db->get();
        
//        return $query->result_array();
//        $this->db->select("post.post_id, post.title, post.summary, 
//                post.date_time_pub, post.full_text, DATE(post.date_time_pub),
//                post.date_time_modified,
//                post.category_id, post.author, post.status,
//                post.state, user.*
//                
//                FROM group_post
//                NATURAL JOIN groups
//                NATURAL JOIN post
//                NATURAL JOIN user
//                WHERE group_post.group_id = $group_id AND  post.status = 'published'
//                ORDER BY post.date_time_pub DESC");
//        
//       
           // $query = $this->db->get();
         
            if ($query->num_rows() > 0) {
                foreach ($query->result_array() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
    }
    
    
    function get_groups_managed($user_id) {
        $this->db->from('group_admin');
        $this->db->join('groups', 'group_admin.group_id = groups.group_id');
        $this->db->join('user', 'group_admin.user_id = user.user_id');
        $where = "group_admin.user_id = $user_id AND groups.status != 'deleted'";
        $this->db->where($where);
        $this->db->order_by('groups.group_name');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_subscribed_groups($user_id) {
        $this->db->from('group_admin');
        $this->db->join('groups', 'group_admin.group_id = groups.group_id');
        $this->db->join('user', 'group_admin.user_id = user.user_id');
        $where = "group_admin.user_id = $user_id AND groups.status != 'deleted'";
        $this->db->where($where);
        $this->db->order_by('groups.group_name');
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    function get_all_subscribed_groups($user_id) {
        $this->db->select('DISTINCT(group_name), groups.group_id');
        $this->db->from('user');
        $this->db->join('subscriber', 'user.user_id = subscriber.user_id');
        $this->db->join('groups', 'subscriber.group_id = groups.group_id');
        $this->db->join('group_post', 'groups.group_id = groups.group_id');
        $this->db->join('post', 'group_post.post_id = post.post_id');
        $this->db->where('user.user_id', $user_id);
        $query = $this->db->get();
        
        return $query->result_array();
    }
    
    
    function get_all_employees() {
        $query = "(SELECT lname, fname, mname, user_id
                    FROM user
                    NATURAL JOIN user_role
                    NATURAL JOIN role
                    WHERE ((user_role.role_id = 1 OR role.rolename = 'admin')
                    AND status != 'deleted'))
                  UNION
                  (SELECT lname, fname, mname, user_id
                    FROM user
                    NATURAL JOIN user_role
                    NATURAL JOIN role
                    WHERE ((user_role.role_id = 2 OR role.rolename = 'author')
                    AND status != 'deleted'))";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    function get_admins_only() {
        $query = "SELECT lname, fname, mname, user_id
                    FROM user
                    NATURAL JOIN user_role
                    NATURAL JOIN role
                    WHERE ((user_role.role_id = 1 OR role.rolename = 'admin')
                    AND status != 'deleted')";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    
    function get_authors_only() {
        $query = "SELECT lname, fname, mname, user_id
                    FROM user
                    NATURAL JOIN user_role
                    NATURAL JOIN role
                    WHERE ((user_role.role_id = 2 OR role.rolename = 'author')
                    AND status != 'deleted')";
        $q = $this->db->query($query);
        return $q->result_array();
    }
    

}

?>
